package com.yixin.vuehr.util;

import com.yixin.vuehr.model.Department;
import com.yixin.vuehr.model.Employee;
import com.yixin.vuehr.model.JObLevel;
import com.yixin.vuehr.model.Nation;
import com.yixin.vuehr.model.Politicsstatus;
import com.yixin.vuehr.model.Position;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class POIUtils {

    public static ResponseEntity<byte[]> employee2Excle(List<Employee> list) {
        //1.创建excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        //2.创建文档摘要
        workbook.createInformationProperties();
        //3.获取并配置文档信息
        DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();
        docInfo.setCategory("员工信息");
        docInfo.setManager("yixinge");
        docInfo.setCompany("yixin.com");
        //4.获取文档摘要信息
        SummaryInformation summInfo = workbook.getSummaryInformation();
        summInfo.setTitle("员工信息表");
        summInfo.setAuthor("yixinge");
        summInfo.setComments("本文档由 一鑫哥 提供");
        HSSFSheet sheet = workbook.createSheet("员工信息表");
        sheet.setColumnWidth(1,12*256);
        sheet.setColumnWidth(2,12*256);
        sheet.setColumnWidth(3,12*256);
        sheet.setColumnWidth(4,12*256);
        sheet.setColumnWidth(5,12*256);
        sheet.setColumnWidth(6,12*256);
        sheet.setColumnWidth(7,12*256);
        sheet.setColumnWidth(8,12*256);
        sheet.setColumnWidth(9,12*256);
        sheet.setColumnWidth(10,12*256);
        sheet.setColumnWidth(11,12*256);
        sheet.setColumnWidth(12,12*256);
        sheet.setColumnWidth(13,12*256);
        sheet.setColumnWidth(14,12*256);
        sheet.setColumnWidth(15,12*256);
        sheet.setColumnWidth(16,12*256);
        sheet.setColumnWidth(17,12*256);
        sheet.setColumnWidth(18,12*256);
        sheet.setColumnWidth(19,12*256);
        sheet.setColumnWidth(20,12*256);
        sheet.setColumnWidth(21,12*256);
        sheet.setColumnWidth(22,12*256);
        sheet.setColumnWidth(23,12*256);
        sheet.setColumnWidth(24,12*256);
        sheet.setColumnWidth(25,12*256);
        //5.创建样式
        //标题行样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HSSFCellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
        //6.创建标题行
        HSSFRow row0 = sheet.createRow(0);
        HSSFCell cell0 = row0.createCell(0);
        cell0.setCellValue("编号");
        cell0.setCellStyle(headerStyle);
        HSSFCell cell1 = row0.createCell(1);
        cell1.setCellStyle(headerStyle);
        cell1.setCellValue("姓名");
        HSSFCell cell2 = row0.createCell(2);
        cell2.setCellStyle(headerStyle);
        cell2.setCellValue("工号");
        HSSFCell cell3 = row0.createCell(3);
        cell3.setCellStyle(headerStyle);
        cell3.setCellValue("性别");
        HSSFCell cell4 = row0.createCell(4);
        cell4.setCellStyle(headerStyle);
        cell4.setCellValue("出生日期");
        HSSFCell cell5 = row0.createCell(5);
        cell5.setCellStyle(headerStyle);
        cell5.setCellValue("身份证号");
        HSSFCell cell6 = row0.createCell(6);
        cell6.setCellStyle(headerStyle);
        cell6.setCellValue("婚姻状况");
        HSSFCell cell7 = row0.createCell(7);
        cell7.setCellStyle(headerStyle);
        cell7.setCellValue("民族");
        HSSFCell cell8 = row0.createCell(8);
        cell8.setCellStyle(headerStyle);
        cell8.setCellValue("籍贯");
        HSSFCell cell9 = row0.createCell(9);
        cell9.setCellStyle(headerStyle);
        cell9.setCellValue("政治面貌");
        HSSFCell cell10 = row0.createCell(10);
        cell10.setCellStyle(headerStyle);
        cell10.setCellValue("电子邮件");
        HSSFCell cell11 = row0.createCell(11);
        cell11.setCellStyle(headerStyle);
        cell11.setCellValue("电话号码");
        HSSFCell cell12 = row0.createCell(12);
        cell12.setCellStyle(headerStyle);
        cell12.setCellValue("联系地址");
        HSSFCell cell13 = row0.createCell(13);
        cell13.setCellStyle(headerStyle);
        cell13.setCellValue("所属部门");
        HSSFCell cell14 = row0.createCell(14);
        cell14.setCellStyle(headerStyle);
        cell14.setCellValue("职位");
        HSSFCell cell15 = row0.createCell(15);
        cell15.setCellStyle(headerStyle);
        cell15.setCellValue("职称");
        HSSFCell cell16 = row0.createCell(16);
        cell16.setCellStyle(headerStyle);
        cell16.setCellValue("聘用形式");
        HSSFCell cell17 = row0.createCell(17);
        cell17.setCellStyle(headerStyle);
        cell17.setCellValue("入职日期");
        HSSFCell cell18 = row0.createCell(18);
        cell18.setCellStyle(headerStyle);
        cell18.setCellValue("转正日期");
        HSSFCell cell19 = row0.createCell(19);
        cell19.setCellStyle(headerStyle);
        cell19.setCellValue("合同起始日期");
        HSSFCell cell20 = row0.createCell(20);
        cell20.setCellStyle(headerStyle);
        cell20.setCellValue("合同截至日期");
        HSSFCell cell21 = row0.createCell(21);
        cell21.setCellStyle(headerStyle);
        cell21.setCellValue("合同期限");
        HSSFCell cell22 = row0.createCell(22);
        cell22.setCellStyle(headerStyle);
        cell22.setCellValue("最高学历");
        HSSFCell cell23 = row0.createCell(23);
        cell23.setCellStyle(headerStyle);
        cell23.setCellValue("专业");
        HSSFCell cell24 = row0.createCell(24);
        cell24.setCellStyle(headerStyle);
        cell24.setCellValue("毕业院校");

        for (int i = 0; i < list.size(); i++) {
            Employee emp = list.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(i+1);
            row.createCell(1).setCellValue(emp.getName());
            row.createCell(2).setCellValue(emp.getWorkid());
            row.createCell(3).setCellValue(emp.getGender());
            HSSFCell c4 = row.createCell(4);
            c4.setCellValue(emp.getBirthday());
            c4.setCellStyle(dataStyle);
            row.createCell(5).setCellValue(emp.getIdcard());
            row.createCell(6).setCellValue(emp.getWedlock());
            row.createCell(7).setCellValue(emp.getNation().getName());
            row.createCell(8).setCellValue(emp.getNativeplace());
            row.createCell(9).setCellValue(emp.getPoliticsstatus().getName());
            row.createCell(10).setCellValue(emp.getEmail());
            row.createCell(11).setCellValue(emp.getPhone());
            row.createCell(12).setCellValue(emp.getAddress());
            row.createCell(13).setCellValue(emp.getDepartment().getName());
            row.createCell(14).setCellValue(emp.getPosition().getName());
            row.createCell(15).setCellValue(emp.getjObLevel().getName());
            row.createCell(16).setCellValue(emp.getEngageform());
            HSSFCell c17 = row.createCell(17);
            c17.setCellValue(emp.getBegindate());
            c17.setCellStyle(dataStyle);
            HSSFCell c18 = row.createCell(18);
            c18.setCellValue(emp.getConversiontime());
            c18.setCellStyle(dataStyle);
            HSSFCell c19 = row.createCell(19);
            c19.setCellValue(emp.getBegincontract());
            c19.setCellStyle(dataStyle);
            HSSFCell c20 = row.createCell(20);
            c20.setCellValue(emp.getEndcontract());
            c20.setCellStyle(dataStyle);
            row.createCell(21).setCellValue(emp.getContractterm());
            row.createCell(22).setCellValue(emp.getTiptopdegree());
            row.createCell(23).setCellValue(emp.getSpecialty());
            row.createCell(24).setCellValue(emp.getSchool());
        }

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        HttpHeaders headers = new HttpHeaders();
        try {
            headers.setContentDispositionFormData("attachment",new String("员工表.xls".getBytes("UTF-8"),"ISO-8859-1"));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            workbook.write(baos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(),headers, HttpStatus.CREATED);
    }

    public static List<Employee> excel2Employee(MultipartFile file, List<Nation> allNation, List<Politicsstatus> allPoliticsstatus, List<Department> allDepartments, List<Position> allPositions, List<JObLevel> allJObLevels) {
        List<Employee> list = new ArrayList<>();
        Employee emp = null;
        try {
            //1创建workbook对象
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            //2获取表单 数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0;i<numberOfSheets;i++){
                //3获取表单
                HSSFSheet sheet = workbook.getSheetAt(i);
                //4获取行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    //跳过标题行
                    if (j == 0){
                        continue;
                    }
                    //获取行
                    HSSFRow row = sheet.getRow(j);
                    if (row==null){
                        continue;//防止中间有空行
                    }
                    //获取列
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    emp = new Employee();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        switch (cell.getCellType()){
                            case STRING:
                                String cellValue = cell.getStringCellValue();
                                switch (k){
                                    case 1:
                                        emp.setName(cellValue);
                                        break;
                                    case 2:
                                        emp.setWorkid(cellValue);
                                        break;
                                    case 3:
                                        emp.setGender(cellValue);
                                        break;
                                    case 5:
                                        emp.setIdcard(cellValue);
                                        break;
                                    case 6:
                                        emp.setWedlock(cellValue);
                                        break;
                                    case 7:
                                        int i1 = allNation.indexOf(new Nation(cellValue));
                                        emp.setNationid(allNation.get(i1).getId());
                                        break;
                                    case 8:
                                        emp.setNativeplace(cellValue);
                                        break;
                                    case 9:
                                        int i2 = allPoliticsstatus.indexOf(new Politicsstatus(cellValue));
                                        emp.setPoliticid(allPoliticsstatus.get(i2).getId());
                                        break;
                                    case 10:
                                        emp.setEmail(cellValue);
                                        break;
                                    case 11:
                                        emp.setPhone(cellValue);
                                        break;
                                    case 12:
                                        emp.setAddress(cellValue);
                                        break;
                                    case 13:
                                        int i3 = allDepartments.indexOf(new Department(cellValue));
                                        emp.setDepartmentid(allDepartments.get(i3).getId());
                                        break;
                                    case 14:
                                        int i4 = allPositions.indexOf(new Position(cellValue));
                                        emp.setPosid(allPositions.get(i4).getId());
                                        break;
                                    case 15:
                                        int i5 = allJObLevels.indexOf(new JObLevel(cellValue));
                                        emp.setJoblevelid(allJObLevels.get(i5).getId());
                                        break;
                                    case 16:
                                        emp.setEngageform(cellValue);
                                        break;
                                    case 22:
                                        emp.setTiptopdegree(cellValue);
                                        break;
                                    case 23:
                                        emp.setSpecialty(cellValue);
                                        break;
                                    case 24:
                                        emp.setSchool(cellValue);
                                        break;
                                }
                                break;
                            default:{
                                switch (k){
                                    case 4:
                                        emp.setBirthday(cell.getDateCellValue());
                                        break;
                                    case 17:
                                        emp.setBegindate(cell.getDateCellValue());
                                        break;
                                    case 18:
                                        emp.setConversiontime(cell.getDateCellValue());
                                        break;
                                    case 19:
                                        emp.setBegincontract(cell.getDateCellValue());
                                        break;
                                    case 20:
                                        emp.setEndcontract(cell.getDateCellValue());
                                        break;
                                    case 21:
                                        emp.setContractterm(cell.getNumericCellValue());
                                        break;
                                }
                                break;
                            }
                        }
                    }
                    list.add(emp);
                }

            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }
}
